package org.niit.dao

import org.niit.util.JDBCUtil
import org.niit.util.ConfigUtil
import java.sql.{Connection, PreparedStatement, Timestamp}
import java.util.Date

/**
 * 离线分析DAO，用于将离线分析结果写入数据库
 */
object OfflineAnalysisDao {

  /**
   * 清空指定表的所有数据
   *
   * @param tableName 表名
   */
  def clearTable(tableName: String): Unit = {
    val sql = s"DELETE FROM $tableName"
    val params = Array.empty[Any]
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 清空所有分析结果表
   */
  def clearAllTables(): Unit = {
    val tables = Array(
      "age_distribution", 
      "gender_ratio", 
      "region_distribution", 
      "category_preference", 
      "price_analysis", 
      "time_period_analysis", 
      "platform_analysis"
    )
    
    tables.foreach(clearTable)
  }

  /**
   * 保存年龄段分布分析结果
   *
   * @param ageRange 年龄段
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveAgeDistribution(ageRange: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("age_distribution")
    val sql = "INSERT INTO age_distribution (age_range, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](ageRange, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存性别比例分析结果
   *
   * @param gender 性别
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveGenderRatio(gender: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("gender_ratio")
    val sql = "INSERT INTO gender_ratio (gender, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](gender, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存地区分布分析结果
   *
   * @param city 城市
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveRegionDistribution(city: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("region_distribution")
    val sql = "INSERT INTO region_distribution (city, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](city, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存菜品类别偏好分析结果
   *
   * @param category 菜品类别
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveCategoryPreference(category: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("category_preference")
    val sql = "INSERT INTO category_preference (category, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](category, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存消费金额分析结果
   *
   * @param priceRange 价格区间
   * @param orderCount 订单数量
   * @param avgPrice 平均价格
   */
  def savePriceAnalysis(priceRange: String, orderCount: Int, avgPrice: Double): Unit = {
    clearTable("price_analysis")
    val sql = "INSERT INTO price_analysis (price_range, order_count, avg_price, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](priceRange, orderCount, avgPrice, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存活跃时段分析结果
   *
   * @param timePeriod 时间段
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def saveTimePeriodAnalysis(timePeriod: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("time_period_analysis")
    val sql = "INSERT INTO time_period_analysis (time_period, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](timePeriod, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 保存平台分析结果
   *
   * @param platform 平台
   * @param orderCount 订单数量
   * @param percentage 百分比
   */
  def savePlatformAnalysis(platform: String, orderCount: Int, percentage: Double): Unit = {
    clearTable("platform_analysis")
    val sql = "INSERT INTO platform_analysis (platform, order_count, percentage, analysis_time) VALUES (?, ?, ?, ?)"
    val params = Array[Any](platform, orderCount, percentage, new Timestamp(new Date().getTime))
    JDBCUtil.executeUpdate(sql, params)
  }

  /**
   * 批量保存分析结果
   *
   * @param tableName 表名
   * @param data 数据列表，每个元素是一行数据
   */
  def batchSaveAnalysisResult(tableName: String, columns: String, data: List[Array[Any]]): Unit = {
    clearTable(tableName)
    val sql = s"INSERT INTO $tableName ($columns) VALUES (${("?" * data.head.length).split("").mkString(", ")})"
    JDBCUtil.executeBatchUpdate(sql, data)
  }
} 